package org.i_plus.model.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import org.i_plus.model.ChatBean;

public class ChatDAO_JDBC {
	 
	private static DataSource ds = null;
//	static {
//		try {
//			Context ctx = new InitialContext();
//			ds = (DataSource) ctx.lookup("java:comp/env/jdbc/TestDB");
//		} catch (NamingException e) {
//			e.printStackTrace();
//		}
//	}
	
	private static final String INSER="INSERT INTO Chat (c_fromacc,c_toacc,c_content,c_time,c_situation) VALUES (?, ?, ?,getdate(),'n');";
	private static final String UPDATE="UPDATE Chat set c_situation='t' where  (c_fromacc = ? and c_toacc = ?)or (c_fromacc = ? and c_toacc = ?)";
	private static final String DELETE="DELETE FROM Chat where c_fromacc = ?";
	private static final String SELECT="SELECT c_fromacc,c_toacc,c_content,c_time,c_situation FROM Chat order by c_fromacc";
	private static final String SELECT_BYFA="SELECT * FROM (SELECT c_toacc,c_content,c_time,c_situation,c_fromacc, ROW_NUMBER() OVER (ORDER BY c_time DESC) AS ROW_NUMBER FROM Chat WHERE (c_fromacc = ? and c_toacc = ?)or (c_fromacc = ? and c_toacc = ?))AS NEWTABLE WHERE ROW_NUMBER BETWEEN 1 AND (?*10) ORDER BY c_time";
	
		public void insert(ChatBean bean){
			Connection conn = null;
			PreparedStatement pst = null;			
			try {
				//conn = ds.getConnection();
				conn = DriverManager.getConnection("jdbc:sqlserver://localhost:1433;database=LovePlusPlus", "sa", "passw0rd");
				pst = conn.prepareCall(INSER);
				pst.setString(1,bean.getC_fromacc());
				pst.setString(2, bean.getC_toacc());
				pst.setString(3,bean.getC_content());
				pst.executeUpdate();
			} catch (SQLException e) {
				e.printStackTrace();
			} finally{
				if(pst != null){
					try {
						pst.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
				if(conn != null){
					try {
						conn.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
			}
			
		}
		
		public void update(ChatBean bean){
			Connection conn = null;
			PreparedStatement pst = null;			
			try {
				//conn = ds.getConnection();
				conn = DriverManager.getConnection("jdbc:sqlserver://localhost:1433;database=LovePlusPlus", "sa", "passw0rd");
				pst = conn.prepareCall(UPDATE);
				pst.setString(1, bean.getC_toacc());
				pst.setString(2,bean.getC_fromacc());
				pst.setString(3, bean.getC_fromacc());
				pst.setString(4,bean.getC_toacc());
				pst.executeUpdate();
			} catch (SQLException e) {
				e.printStackTrace();
			} finally{
				if(pst != null){
					try {
						pst.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
				if(conn != null){
					try {
						conn.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
			}
			
		}
		
		public void delete(String c_fromacc){
			Connection conn = null;
			PreparedStatement pst = null;
			
			try {
				//conn = ds.getConnection();
				conn = DriverManager.getConnection("jdbc:sqlserver://localhost:1433;database=LovePlusPlus", "sa", "passw0rd");
				pst = conn.prepareCall(DELETE);
				pst.setString(1, c_fromacc);
				pst.executeUpdate();
			} catch (SQLException e) {
				e.printStackTrace();
			} finally{
				if(pst != null){
					try {
						pst.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
				if(conn != null){
					try {
						conn.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
			}
		}
		
		public List<ChatBean> select(){
			List<ChatBean> result = new ArrayList<ChatBean>();
			Connection conn = null;
			PreparedStatement pst = null;	
			ResultSet rs = null;
			try {
				//conn = ds.getConnection();
				conn = DriverManager.getConnection("jdbc:sqlserver://localhost:1433;database=LovePlusPlus", "sa", "passw0rd");
				pst = conn.prepareCall(SELECT);
				rs =pst.executeQuery();
				while(rs.next()){
					ChatBean bean = new ChatBean();
					bean.setC_fromacc(rs.getString(1));
					bean.setC_toacc(rs.getString(2));
					bean.setC_content(rs.getString(3));
					bean.setC_time(rs.getTimestamp(4));
					bean.setC_situation(rs.getString(5));
					result.add(bean);
				}
			} catch (SQLException e) {
				e.printStackTrace();
			} finally{
				if(rs != null){
					try {
						rs.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
				if(pst != null){
					try {
						pst.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
				if(conn != null){
					try {
						conn.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
			}
			return result;
		}
		
		public List<ChatBean> selectByFromAcc(String fromacc,String toacc,int page){
			Connection conn = null;
			PreparedStatement pst = null;
			ResultSet rs = null;
			List<ChatBean> result = new ArrayList<ChatBean>();
			try {
				//conn = ds.getConnection();
				conn = DriverManager.getConnection("jdbc:sqlserver://localhost:1433;database=LovePlusPlus", "sa", "passw0rd");
				pst = conn.prepareCall(SELECT_BYFA);
				pst.setString(1, fromacc);
				pst.setString(2, toacc);
				pst.setString(3, toacc);
				pst.setString(4, fromacc);
				pst.setInt(5, page);
				rs =pst.executeQuery();
				while(rs.next()){
				ChatBean bean = new ChatBean();
				bean.setC_toacc(rs.getString(1));
				bean.setC_content(rs.getString(2));
				bean.setC_time(rs.getTimestamp(3));
				bean.setC_situation(rs.getString(4));
				bean.setC_fromacc(rs.getString(5));
				result.add(bean);
				}
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				if(rs != null){
					try {
						rs.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
				if(pst != null){
					try {
						pst.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
				if(conn != null){
					try {
						conn.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
			}
			return result;
		}
		public static void main(String args[]){
			ChatDAO_JDBC dao = new ChatDAO_JDBC();
//			List<ChatBean> list = dao.selectByFromAcc("a19897811@yahoo.com", "aa",1);
//			for(ChatBean temp:list)
//			System.out.println(temp.getC_fromacc()+":"+temp.getC_toacc()+":"+temp.getC_content());
			
			ChatBean bean = new ChatBean();
			bean.setC_fromacc("a19897811@yahoo.com");
			bean.setC_toacc("aa");
			bean.setC_content("hahahahahahaha");
			dao.insert(bean);
			
			
		}
		
}
